<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>CS 143 Project 1B Web Query Interface</title>
</head>

<body>
<h1>CS 143 Project 1B Web Query Interface</h1>
<p>By James Hung (ID: 203762477)</p>
<p><strong>Please enter a SQL query below, and click Submit.</strong></p>
<ul>
  <li>Tables and fields are case-sensitive.</li>
  <li>Use <code>SHOW TABLES</code> to see the list of available tables.</li>
</ul>
<?php
	$db_connection = NULL;
	$set_error_handler = false;
	$printed_result_header = false;

	function cleanup()
	{
		global $db_connection, $set_error_handler;
		if ($set_error_handler) {
			restore_error_handler();
			$set_error_handler = false;
		}
		if ($db_connection) {
			mysql_close($db_connection);
			$db_connection = NULL;
		}
	}

	function print_result($message)
	{
		global $printed_result_header;
		if (!$printed_result_header) {
			echo "<h2>Result</h2>";
			$printed_result_header = true;
		}
		echo $message;
	}

	function print_error($errstr)
	{
		print_result("<p>An error has occurred. Error details:</p><p><code>" . $errstr . "</code></p>");
	}

	function my_error_handler($errno, $errstr, $errfile, $errline)
	{
		print_error($errstr);
		cleanup();
		exit();
	}

	$query = $_GET["query"];

	echo "<form method=\"get\"><p><textarea name=\"query\" cols=\"80\" rows=\"5\">" . $query . "</textarea></p><p><input type=\"submit\" value=\"Submit\"/></p></form>";

	if (strlen($query) <= 0) {
		cleanup();
		return;
	}

	if (!preg_match("#^\s*(SELECT|SHOW)#i", $query)) {
		print_result("Please enter only valid <code>SELECT</code> or <code>SHOW</code> queries!");
		cleanup();
		return;
	}

	set_error_handler("my_error_handler");
	$set_error_handler = true;

	$db_connection = mysql_connect("localhost", "cs143", "");
	if (!$db_connection) {
		print_error(mysql_error($db_connection));
		cleanup();
		return;
	}

	if (!mysql_select_db("CS143", $db_connection)) {
		print_error(mysql_error($db_connection));
		cleanup();
		return;
	}

	$result = mysql_query($query, $db_connection);
	if (!$result) {
		print_error(mysql_error($db_connection));
		cleanup();
		return;
	}

	print_result("<table border=\"1\" cellpadding=\"5\">");

	// Print column headers.
	print_result("<tr>");
	for ($i = 0; $i < mysql_num_fields($result); $i++)
		print_result("<td align=\"center\"><strong>" . mysql_field_name($result, $i) . "</string></td>");
	print_result("</tr>");

	// Print tuples.
	while ($row = mysql_fetch_row($result)) {
		print_result("<tr>");
		for ($i = 0; $i < count($row); $i++) {
			$col = $row[$i];
			if (!$col)
				$col = "N/A";
			print_result("<td>" . $col . "</td>");
		}
		print_result("</tr>");
	}

	print_result("</table>");

	cleanup();
?>
</body>
</html>
